from helpers import create_database_engine, run_sql_and_return_df, run_sql_and_return_html, create_db_wrapper, execute_ddl_from_file
from itables import show
# Load these variables from .env file.
config_map = {
'user': "MYSQL_USERNAME",
'password': "MYSQL_PASSWORD",
'host': "MYSQL_HOSTNAME",
'database': "HW6_DB_NAME"
}
cnx,config = create_db_wrapper( config_map )
def show_result_set( sql, **kwargs ):
df = run_sql_and_return_df( cnx,sql )
show(df,**kwargs)Homework 6 - Creating a resume database
This document contains information about a database containing people, skills, and peopleskills tables. These tables are represented as a Crows-Foot diagram and some lists along with descriptions for those sections. At the end there is a reflection and a read me.
GITHUB URL: https://github.com/cmsc-vcu/cmsc408-fa2025-hw6-CaptnKristmas
Overview and description
The database contains 3 tables, people, skills, and peopleskills. The first covers the employees of the company, the second possible skills, and last tracks the skills earned. As such there is a relationship between people and peopleskills as well as skills and peopleskills. This allows us to connect the skills possibly earned with the employees and store what people have earned in people skills.
We were tasked with creating this database using mySQL, Python, a poetry shell, and multiple libraries listed in the readme. Following this we were tasked with completing this report that breaks down contents of the database via a Crows-Foot diagram, descriptions and some lists. Upon completion we rendered this document as an html that you may view.
Crows-foot diagram
This diagram represents the current database as a whole. Do to limitations we are unable to represent nullable and non nullable values and are also unable to represent varchar directly. This is instead just ignored and replaced with string respectively. We are able to represent the foreign keys and the primary keys on the diagram.
The connection between people and peopleskills as well as skills and peopleskills is represented via the diagram. It shows how exactly one person can have multiple skills tracked and exactly one skill can be tracked across many people.
erDiagram
PEOPLE ||--o{ PEOPLESKILLS : "has"
SKILLS ||--o{ PEOPLESKILLS : "tracks"
PEOPLE {
int id PK
string first_name
string last_name
string email
string linkedin_url
string headshot_url
string discord_handle
string brief_bio
date date_joined
}
SKILLS {
int id PK
string name
string description
string tag
string url
string time_commitment
}
PEOPLESKILLS {
int id PK
int skills_id FK
int people_id FK
date date_acquired
}
Loading the database
ddl_file_name = "./my-ddl.sql"
messages,errors = execute_ddl_from_file( ddl_file_name, cnx)
if errors:
for error in errors:
print(f"{error}<br/>")
else:
print(f"No errors detected while loading: {ddl_file_name}")No errors detected while loading: ./my-ddl.sql
Examples of data in the database
The following sections provide an overview of the schema including table names, and number of rows and columns in each table.
For each table, a description of each table is presented along with it’s contents.
Tables and metrics in the database
| TableName | RecordCount | ColumnCount |
|---|---|---|
| people | 10 | 9 |
| peopleskills | 26 | 4 |
| skills | 8 | 6 |
| Total Rows: 3, Total Columns: 3 | ||
x = show_result_set( sql )| Loading ITables v2.5.2 from the internet... (need help?) |
People table
The people table is a list of employees at the company currently. It includes the id, first name, last name, email, linkedin url (if they have one), headshot url (if they have one), discord handle (if they have one), brief bio (if included), and the date joined at the company.
Below is data currently in the people table.
show_result_set( f"""
select * from people;
""" )| Loading ITables v2.5.2 from the internet... (need help?) |
Skills table
The skills table is a list of skills that employees might have at this company. It includes the name, description, tag (for reference), url link for information, and estimated time commitment needed to gain the skill.
Below is data currently in the skills table.
show_result_set( f"""
select * from skills
""" )| Loading ITables v2.5.2 from the internet... (need help?) |
PeopleSkills table
The peopleskills table is a link between emplopyees and skills. The table shows what skills have been acquired by which employees along with the date acquired. It allows the company to keep track of progress and assists in project assignment.
Below is data currently in the peopleskills table.
show_result_set(f"""
select * from peopleskills
""" )| Loading ITables v2.5.2 from the internet... (need help?) |
Queries
List skill id, name and tag ordered by name
show_result_set( f"""
select id, name, tag from skills order by name
""" )| Loading ITables v2.5.2 from the internet... (need help?) |
List people names and email addresses ordered by last_name
show_result_set( f"""
select first_name, last_name, email
from people
order by last_name
""" )| Loading ITables v2.5.2 from the internet... (need help?) |
List skill names of Person 1
show_result_set( f"""
select s.name as skill_name, ps. date_acquired
from peopleskills ps
join people p on ps.people_id = p.id
join skills s on ps.skills_id = s.id
where p.last_name = 'Person 1'
order by ps.date_acquired;
""" )| Loading ITables v2.5.2 from the internet... (need help?) |
List people names with Skill 6
show_result_set( f"""
select p.first_name, p.last_name,
ps.date_acquired
from people p
join peopleskills ps on p.id = ps.people_id
where ps.skills_id = 6
order by date_acquired;
""" )| Loading ITables v2.5.2 from the internet... (need help?) |
List names and email addresses of people without skills
show_result_set( f"""
select p.first_name, p.last_name, p.email
from people p
left join peopleskills ps on p.id =
ps.people_id
where ps.id is null
order by last_name;
""" )| Loading ITables v2.5.2 from the internet... (need help?) |
Reflection
The assignment provides me with wonderful skills that are applicable to personal projects, organization or working at my future career. At the same time, there were major issues that crept up even after set up with dependencies randomly showing up as not installed that required uninstalls and restarts of Virtual Studio Code then reinstalls to fix. Took a while to discover this fix which added to the struggle quite a bit. This simply leads me to believe its more efficient to constantly update important code to new versions as opposed to dealing with poetry, pyenv and managing version control at all.
README
Homework 6 — Creating a Resume Database
Author: Vladimir Paraschiv
Course: CMSC 408 – Database Systems
Semester: Fall 2025
Date: October 24, 2025
Repository
📘 Overview
This project documents a Resume Database built from scratch using MySQL, Python, and Quarto. The system models individuals, their professional skills, and the skills each person has acquired over time.
The database consists of three key tables: - people – representing individuals or employees
- skills – cataloging skills with descriptions and tags
- peopleskills – a junction table linking people and skills with acquisition dates
The report demonstrates the design and relationships among these tables using a Crow’s Foot ER diagram, while showcasing SQL queries that retrieve and analyze the stored data. It integrates direct MySQL queries, Python data rendering, and interactive HTML output generated through Quarto.
🧩 Tools and Technologies
This project uses:
- Quarto — for dynamic report generation combining SQL, Python, and Markdown
- Mermaid — for the Crow’s Foot ER diagram representation
- Python (3.11.6) — for SQL execution and data retrieval
- itables — to display interactive HTML tables inside the report
- SQLAlchemy / PyMySQL / dotenv — to handle MySQL connections and environment variables
- Poetry — to manage dependencies and virtual environments
- Pyenv — to isolate interpreter versions for consistent runtime behavior
🧠 Database Summary
The Resume Database contains three relational tables that together capture who works at the company, which skills exist, and who has acquired which skills:
- people — stores employee demographic and contact information
- skills — defines a catalog of technical and professional skills
- peopleskills — records which people have which skills, and the date acquired
This schema forms a many-to-many relationship between people and skills, implemented via the peopleskills junction table.
Referential integrity is enforced through foreign keys: - peopleskills.people_id → people.id
- peopleskills.skills_id → skills.id
🧩 Repository Structure
cmsc408-fa2025-hw6-CaptnKristmas/
│
├── .vscode/ # VS Code workspace settings
│ └── settings.json # Python interpreter and linting settings
│
├── reports/
│ ├── report.qmd # Quarto report (main deliverable)
│ ├── _quarto.yml # Quarto project metadata
│ ├── my-ddl.sql # SQL DDL for table creation and inserts
│ ├── helpers.py # Helper utilities for connecting and querying MySQL
│ ├── _init__.py # (Marks reports as a package)
│ ├── .env # Environment variables for database connection
│ ├── .gitignore # Ignore files for Git
│ ├── report.html # Rendered HTML output
│ ├── report_files\libs # Quarto generated supporting files
│ └── _pycache__/
│ ├── helpers.cpython-310.pyc # Compiled helper utilities
│ └── helpers.cpython-311.pyc # Compiled helper utilities
│
├── pyproject.toml # Poetry environment definition
├── poetry.lock # Poetry dependency lock file
├── .gitignore # Ignore files for Git
└── readME.md # This documentation file
⚙️ Setup Instructions for Fresh Development Environment
1. Environment Setup
Ensure you have the following installed:
- Python 3.11+
- Poetry
- MySQL Server
- Quarto
- VS Code
Activate the Poetry environment:
poetry shellInstall dependencies:
poetry install2. Configure Environment Variables
Create a .env file in the reports/ directory with your MySQL credentials:
MYSQL_USER=your_username
MYSQL_PASSWORD=your_password
MYSQL_HOST=localhost
MYSQL_DB=your_database_name
3. Database Setup
Run the SQL DDL script to create tables and insert data.
4. Load Database
Execute the provided Python script to access and retrieve data from the database.
Expected output: No errors detected while loading: ./my-ddl.sql
🧪 Rendering Instructions
To render the Quarto report into a standalone HTML document, run in Virtual Studio Code terminal: cd ./reports and then: quarto render report.qmd
Alternatively, simply open the pre-rendered report.html file included in this repository.